/**
 * 
 */
package net.xfok.db;

import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.Iterator;
import java.util.Map;

/**
 * @author howard
 *
 */
public class Query {
	@SuppressWarnings("unchecked")
	public static String create(Object obj, String tableName) {
		StringBuffer query = new StringBuffer();
		query.append("CREATE TABLE IF NOT EXISTS [" + tableName + "](");
		Map<String,String> map = Reflect.genFields(obj);
		Iterator<?> it = map.entrySet().iterator();
		int i = 1;
		while (it.hasNext()) {
			Map.Entry entry = (Map.Entry) it.next();
			String fieldName = entry.getKey().toString();
			String fieldType = entry.getValue().toString();
			query.append("[").append(fieldName).append("] ");
			if (fieldType.equals(ORM.FIELD_TYPE_INT)) {
				query.append(ORM.COLUMN_TYPE_INTEGER+" ");
			}
			if (fieldType.equals(ORM.FIELD_TYPE_LONG)){
				query.append(ORM.COLUMN_TYPE_BIGINT+" ");
			}
			if (fieldType.equals(ORM.FIELD_TYPE_STRING)&&fieldName.indexOf(ORM.FIELD_TEXT_NAME)<0) {
				query.append(""+ORM.COLUMN_TYPE_VARCHAR+"("+ORM.DEFAULT_VARCHAR_LONG+") ");
			}
			if(fieldName.indexOf(ORM.FIELD_TEXT_NAME)>=0){
				query.append(ORM.COLUMN_TYPE_TEXT+" ");
			}
			if (fieldType.equals(ORM.FIELD_TYPE_DATE)) {
				query.append(ORM.COLUMN_TYPE_DATETIME+" ");
			}
			if (fieldType.equals(ORM.FIELD_TYPE_FLOAT)) {
				query.append(ORM.COLUMN_TYPE_FLOAT+" ");
			}
			if (fieldName.equals(ORM.FIELD_ID_NAME)) {
				query.append(ORM.CONSTRAINT_PRIMARY_KEY+" ");
				query.append(ORM.CONSTRAINT_NOT_NULL);
			}
			if (i < map.size()) {
				query.append(",");
			}
			i++;
		}
		query.append(");");
		return query.toString();
	}

	public static String drop(String tableName){
		return "DROP TABLE IF EXISTS " + tableName;
	}
	
	public static String insert(Object obj, String tableName) {
		StringBuffer query = new StringBuffer();
		try {
			query.append("insert into " + tableName + " values (");
			Field[] fields = Reflect.genFileds(obj);
			int i = 1;
			for (Field field : fields) {
				Object value = Reflect.invokeGet(obj, field);
				String type=field.getType().getSimpleName();
				if (type.equals(ORM.FIELD_TYPE_INT)||
						type.equals(ORM.FIELD_TYPE_FLOAT)||
						type.equals(ORM.FIELD_TYPE_LONG)) {
					if (field.getName().equals(ORM.FIELD_ID_NAME)) {
						query.append(ORM.FIELD_ID_VALUE);
					} else {
						query.append(value);
					}
				}else {
					if(type.equals(ORM.FIELD_TYPE_DATE)){
						value=new SimpleDateFormat(ORM.DEFAULT_DATE_FORMAT).format(value);
					}
					query.append("'").append(value).append("'");
				}
				if (i < fields.length) {
					query.append(",");
				}
				i++;
			}
			query.append(");");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return query.toString();
	}

	public static String update(Object obj, String tableName) {
		StringBuffer query = new StringBuffer();
		try {
			query.append("update " + tableName + " set ");
			Field[] fields = Reflect.genFileds(obj);
			int i = 1;
			String id = null;
			for (Field field : fields) {
				Object value = Reflect.invokeGet(obj, field);
				String type=field.getType().getSimpleName();
				if (type.equals(ORM.FIELD_TYPE_INT)||
						type.equals(ORM.FIELD_TYPE_FLOAT)||
						type.equals(ORM.FIELD_TYPE_LONG)) {
					if (field.getName().equals(ORM.FIELD_ID_NAME)) {
						id=value.toString();
					} else {
						query.append(field.getName()).append("=").append(value);
					}
				}else {
					if(type.equals(ORM.FIELD_TYPE_DATE)){
						value=new SimpleDateFormat(ORM.DEFAULT_DATE_FORMAT).format(value);
					}
					query.append(field.getName()).append("=").append("'")
							.append(value).append("'");
				}
				if (i < fields.length && !field.getName().equals(ORM.FIELD_ID_NAME)) {
					query.append(",");
				}
				i++;
			}
			query.append(" where "+ORM.FIELD_ID_NAME+"=").append(id);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return query.toString();
	}

	public static String select(String tableName, int id) {
		String query = "select * from " + tableName + " where "+ORM.FIELD_ID_NAME+"=" + id;
		return query;
	}

	public static String delete(String tableName, int id) {
		String query = "delete from " + tableName + " where "+ORM.FIELD_ID_NAME+"=" + id;
		return query;
	}

	public static String select(String tableName) {
		String query = "select * from " + tableName;
		return query;
	}

	public static String select(String tableName, String fieldName, String key) {
		String query = "select * from " + tableName + " where " + fieldName
				+ " like '%" + key + "%'";
		return query;
	}

	public static String select(String tableName, String condition) {
		String query = "select * from " + tableName + " where " + condition;
		return query;
	}

	public static String select(String tableName, int pageNumber, int pageSize) {
		String query = "select * from " + tableName
				+ " order by id desc limit " + pageSize + " offset "
				+ (pageNumber - 1) * pageSize;
		return query;
	}
	
	public static String select(String tableName, String fieldName, String key,
			int pageNumber, int pageSize) {
		String query = "select * from " + tableName + " where " + fieldName
				+ " like '%" + key + "%'" + " order by id desc limit "
				+ pageSize + " offset " + (pageNumber - 1) * pageSize;
		return query;
	}

	public static String select(String tableName, String condition,
			int pageNumber, int pageSize) {
		String query = "select * from " + tableName + " where " + condition
				+ " order by id desc limit " + pageSize + " offset "
				+ (pageNumber - 1) * pageSize;
		return query;
	}
	
	public static String count(String tableName) {
		String query = "select count(*) from " + tableName;
		return query;
	}

	public static String count(String tableName, String fieldName, String key) {
		String query = "select count(*) from " + tableName + " where " + fieldName
				+ " like '%" + key + "%'";
		return query;
	}

	public static String count(String tableName, String condition) {
		String query = "select count(*) from " + tableName + " where " + condition;
		return query;
	}
	
	public static String isTableExist(String tableName){
		String query="select count(*)from sqlite_master where type='table' and name='"+tableName+"';";
		return query;
	}
}
